package database_package;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.*;
import java.util.Enumeration;
import java.util.HashSet;
import java.util.jar.JarEntry;
import java.util.jar.JarFile;
import java.util.zip.ZipEntry;

import javax.swing.*;

/**
 * This class is to create database and its attributes.
 * 
 * @author Syrym
 * @version 12/12/2014
 * */
public class SqliteConnection {
	/**The Connection instance to database.
	 * @author Syrym*/
	static Connection conn = null;
	/**Path to Temp Directory.
	 * @author Syrym*/
	static String tempDirectory = "";
	/**Path to dbfiles in Temp Directory.
	 * @author Syrym*/
	static String dbfilesPath = "";
	/**Path to Jar file Directory.
	 * @author Syrym*/
	static String jarFilePath = "";

	/**
	 * The utility method to get connected to database.
	 * 
	 * @author Syrym
	 * @return conn is the connection to database.
	 * */
	public static Connection dbConnect() {
		initializePaths();
		try {
			Class.forName("org.sqlite.JDBC");
			conn = DriverManager.getConnection("jdbc:sqlite:" + tempDirectory
					+ "GlobalLogAnalystsDB.sqlite");
		} catch (Exception e) {
			JOptionPane.showMessageDialog(null, "dbConnect() " + e);
		}

		populateDB();
		return conn;
	}
	/**
	 * Method to popluate DataBase.
	 * Creates tables and puts initial 
	 * users and their data. 
	 * @author Syrym
	 * */
	private static void populateDB() {
		DatabaseMetaData dbm;
		try {
			dbm = conn.getMetaData();
			ResultSet tables = dbm.getTables(null, null, "user_credentials",
					null);
			// check if "user_credentials" table is there
			if (!tables.next()) {
				// Table doesnt exists
				createUserCredentialsTable();
				populateUserCredentials();
			}
			tables = dbm.getTables(null, null, "user_rfps_drafts", null);
			if (!tables.next()) {
				// if Table doesnt exist
				createRFPsDraftsTable();
				copyInitFilesToTemp();
				populateRFPsDrafts();

			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}
	/**
	 * Method to initialize
	 * directory paths.
	 * @author Syrym
	 * */
	private static void initializePaths() {
		tempDirectory = System.getProperty("java.io.tmpdir");
		dbfilesPath = reverseToFWDSlashes(tempDirectory) + "dbfiles";
		jarFilePath = SqliteConnection.class.getProtectionDomain().
                    getCodeSource().getLocation().toString().
                    substring(6);
	}
	/**
	 * Method to copy initial users' files 
	 * from JarFile and paste into Temp/dbfiles 
	 * directory.
	 * @author Syrym
	 * */
	private static void copyInitFilesToTemp() {
		try {
			// use file when project is run through eclipse,
			// with jarfile directory path.
			File file = new File("C:/Users/Syrym/Desktop/gapp.jar");
			//if eclipse runs it, then pass file to JarFile argument. 
			//if jarfile runs it, then pass jarFilePath to  JarFile argument
			JarFile jarfile = new JarFile(file);
			copyToTemp(jarfile, dbfilesPath);
		} catch (IOException e) {
			e.printStackTrace();
		}

	}
	/**
	 * Method to return array of initial usernames.
	 * @author Syrym
	 * @return result is array of usernames.
	 * */
	private static HashSet<String> getInitUsers() {
		String[] users = { "john", "jerry", "mary", "sam" };
		HashSet<String> result = new HashSet<String>();
		for (int i = 0; i < users.length; i++) {
			result.add(users[i]);
		}
		return result;
	}
	/**
	 * Method to copy files from JarFile to Temp/dbfiles.
	 * @author Syrym
	 * @param jar is the JarFile to copy from.
	 * @param destDir is the destination path.
	 * */
	private static void copyToTemp(JarFile jar, String destDir) {
		HashSet<String> users = getInitUsers(); 
		for (Enumeration<JarEntry> entries = jar.entries(); entries
				.hasMoreElements();) {
			JarEntry entry = entries.nextElement();

			String entryName = entry.getName();
			int bSlashInd = entryName.indexOf('/');
			String name = "";
			if (bSlashInd >= 0) {
				name = entryName.substring(0, bSlashInd);
			}
			if (users.contains(name) && name.length() + 1 < entryName.length()) {
				try {

					if (!new File(destDir + "/" + name).exists()) {
						new File(destDir + "/" + name).mkdirs();
					}
					File destFile = new File(destDir + "/" + entryName);

					ZipEntry zipentry = jar.getEntry(entryName);

					InputStream in = new BufferedInputStream(
							jar.getInputStream(zipentry));
					OutputStream out = new BufferedOutputStream(
							new FileOutputStream(destFile));

					byte[] buffer = new byte[2048];
					for (;;) {
						int nBytes = in.read(buffer);
						if (nBytes <= 0)
							break;
						out.write(buffer, 0, nBytes);
					}
					out.flush();
					out.close();
					in.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}

	/**
	 * Helper method to convert back slashes to forward slashes.
	 * @author Syrym
	 * @return path is the path with forward slashes
	 * */
	private static String reverseToFWDSlashes(String path) {

		while (path.indexOf('\\') > -1) {
			int BSlashInd = path.indexOf('\\');
			path = path.substring(0, BSlashInd) + "/"
					+ path.substring(++BSlashInd);
		}
		return path;
	}
	/**
	 * Method to create user_credentials table.
	 * @author Syrym
	 * */
	private static void createUserCredentialsTable() {
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
			String sql = "CREATE TABLE user_credentials "
					+ "(username 		VARCHAR PRIMARY KEY  NOT NULL,"
					+ " password       VARCHAR   NOT NULL, "
					+ " isAdmin        BOOLEAN)";
			stmt.executeUpdate(sql);
			stmt.close();
		} catch (Exception e) {
			System.exit(0);
		}
	}
	/**
	 * Method to create user_rfps_drafts table.
	 * @author Syrym
	 * */
	private static void createRFPsDraftsTable() {
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
			String sql = "CREATE TABLE user_rfps_drafts "
					+ "(username 	   VARCHAR  NOT NULL,"
					+ " rfps          VARCHAR  NOT NULL, "
					+ " drafts        VARCHAR,"
					+ " PRIMARY KEY(username, rfps))";
			stmt.executeUpdate(sql);
			stmt.close();
		} catch (Exception e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
			System.exit(0);
		}
	}
	/**
	 * Method to populate user_credentials table
	 * with initial users.
	 * @author Syrym
	 * */
	private static void populateUserCredentials() {
		Statement stmt = null;
		try {
			conn.setAutoCommit(false);

			stmt = conn.createStatement();
			String sql = "INSERT INTO user_credentials (username, password, isAdmin) "
					+ "VALUES ('john', 'pass', 0);";
			stmt.executeUpdate(sql);

			sql = "INSERT INTO user_credentials (username, password, isAdmin) "
					+ "VALUES ('admin', 'adminpass', 1);";
			stmt.executeUpdate(sql);

			sql = "INSERT INTO user_credentials (username, password, isAdmin) "
					+ "VALUES ('mary', 'pass', 0);";
			stmt.executeUpdate(sql);

			sql = "INSERT INTO user_credentials (username, password, isAdmin) "
					+ "VALUES ('jerry', 'pass', 0);";
			stmt.executeUpdate(sql);

			sql = "INSERT INTO user_credentials (username, password, isAdmin) "
					+ "VALUES ('sam', 'pass', 0);";
			stmt.executeUpdate(sql);

			stmt.close();
			conn.commit();
		} catch (Exception e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
			System.exit(0);
		}
	}
	/**
	 * Method to populate user_rfps_drafts table
	 * with initial users' files.
	 * @author Syrym
	 * */
	private static void populateRFPsDrafts() {
		Statement stmt = null;
		try {
			conn.setAutoCommit(false);

			stmt = conn.createStatement();
			String sql = "INSERT INTO user_rfps_drafts (username, rfps, drafts) "
					+ "VALUES ('jerry', '"
					+ dbfilesPath
					+ "/jerry/rfp7.pdf', '"
					+ dbfilesPath
					+ "/jerry/draft 2.docx');";
			stmt.executeUpdate(sql);
			sql = "INSERT INTO user_rfps_drafts (username, rfps, drafts) "
					+ "VALUES ('jerry', '" + dbfilesPath
					+ "/jerry/rfp8.pdf', null);";
			stmt.executeUpdate(sql);
			sql = "INSERT INTO user_rfps_drafts (username, rfps, drafts) "
					+ "VALUES ('jerry', '" + dbfilesPath
					+ "/jerry/rfp3.pdf', '" + dbfilesPath
					+ "/jerry/draft 1.docx');";
			stmt.executeUpdate(sql);

			sql = "INSERT INTO user_rfps_drafts (username, rfps, drafts) "
					+ "VALUES ('john', '" + dbfilesPath + "/john/rfp1.pdf', '"
					+ dbfilesPath + "/john/draft1.docx');";
			stmt.executeUpdate(sql);
			sql = "INSERT INTO user_rfps_drafts (username, rfps, drafts) "
					+ "VALUES ('john', '" + dbfilesPath + "/john/rfp2.pdf', '"
					+ dbfilesPath + "/john/draft2.docx');";
			stmt.executeUpdate(sql);

			sql = "INSERT INTO user_rfps_drafts (username, rfps, drafts) "
					+ "VALUES ('sam', '" + dbfilesPath + "/sam/rfp5.pdf', '"
					+ dbfilesPath + "/sam/draft1.docx');";
			stmt.executeUpdate(sql);
			sql = "INSERT INTO user_rfps_drafts (username, rfps, drafts) "
					+ "VALUES ('sam', '" + dbfilesPath
					+ "/sam/rfp6.pdf', null);";
			stmt.executeUpdate(sql);

			sql = "INSERT INTO user_rfps_drafts (username, rfps, drafts) "
					+ "VALUES ('mary', '" + dbfilesPath + "/mary/rfp4.pdf','"
					+ dbfilesPath + "/john/draft 1.docx');";
			stmt.executeUpdate(sql);

			stmt.close();
			conn.commit();
		} catch (Exception e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
			System.exit(0);
		}
	}
}
